﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using org.in2bits.MyXls;

namespace HouseSecretary.Common
{
    public class Excel
    {
        public Excel()
        {

        }
        /// <summary>
        /// 合并行列的方法
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="xf"></param>
        /// <param name="title"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        private static void MergeRegion(ref Worksheet ws, XF xf, string title, int startRow, int startCol, int endRow, int endCol)
        {
            for (int i = startCol; i <= endCol; i++)
            {
                for (int j = startRow; j <= endRow; j++)
                {
                    ws.Cells.Add(j, i, title, xf);
                }
            }
            ws.Cells.Merge(startRow, endRow, startCol, endCol);
        }
        /// <summary>
        /// 导出excel通用方法
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="excelTitle"></param>
        /// <param name="rowCount"></param>
        /// <param name="xlsxlsModelList"></param>
        public static void ExportToExcelReturnPath(string _mappath, string filename, string excelTitle, int rowCount, List<Model.XlsModel> xlsxlsModelList, List<Model.XlsModel_Column> xlsColList, List<Model.XlsModel_Row> xlsRowList, ref string saveFileName, string _mappath2 = "", bool iszbsj = false)
        {
            //string _filepath = "";
            filename = filename.Replace(".xls", "");
            //filename += Guid.NewGuid();
            if (xlsxlsModelList.Count > 0)
            {
                int _tmpRowNum = xlsxlsModelList[0].RowIndex;
                int _colCount = 0;
                foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
                {
                    if (_xlsModel.RowIndex == _tmpRowNum)
                    {
                        _colCount += _xlsModel.ColSpan;
                    }
                }

                DataTable dt = new DataTable();

                for (int i = 0; i < _colCount; i++)
                {
                    dt.Columns.Add(i.ToString());

                }
                for (int i = 0; i < rowCount; i++)
                {
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < _colCount; j++)
                    {
                        dr[j] = "0";
                    }
                    dt.Rows.Add(dr);

                }


                XlsDocument doc = new XlsDocument();

                doc.FileName = filename + ".xls";
                Worksheet sheet = doc.Workbook.Worksheets.Add("sheet1");
                //列设置
                if (xlsColList != null)
                {
                    foreach (Model.XlsModel_Column xlsCol in xlsColList)
                    {
                        ColumnInfo colInfo = new ColumnInfo(doc, sheet);//生成列格式对象
                        colInfo.ColumnIndexStart = xlsCol.ColumnIndexStart;
                        colInfo.ColumnIndexEnd = xlsCol.ColumnIndexEnd;
                        colInfo.Width = xlsCol.Width;
                        sheet.AddColumnInfo(colInfo);
                    }
                }
                //行设置
                if (xlsRowList != null)
                {
                    foreach (Model.XlsModel_Row xlsRow in xlsRowList)
                    {
                        RowInfo rol = new RowInfo();
                        rol.RowIndexStart = xlsRow.RowIndexStart;
                        rol.RowIndexEnd = xlsRow.RowIndexEnd;
                        rol.RowHeight = xlsRow.RowHeight;
                        sheet.AddRowInfo(rol);
                    }
                }

                XF cellXF = doc.NewXF();//标题
                cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中
                cellXF.Font.Bold = true;
                
                Cell cell;
                int _rownumber = 0;
                if (iszbsj)
                {
                    cell = sheet.Cells.Add(1, 1, excelTitle, cellXF);
                    MergeArea meaA = new MergeArea(1, 1, 1, _colCount);
                    sheet.AddMergeArea(meaA);//填加合并单元格
                }
                //if (_mappath2 != "")
                //{
                //    cellXF = doc.NewXF();//副标题
                //    cellXF.HorizontalAlignment = HorizontalAlignments.Left;//设定文字居中
                //    cell = sheet.Cells.Add(2, 1, _mappath2, cellXF);
                //    meaA = new MergeArea(2, 2, 1, _colCount);
                //    sheet.AddMergeArea(meaA);//填加合并单元格
                //    _rownumber = 2;
                //}
                //if (iszbsj)
                //{
                //    cellXF = doc.NewXF();//指标时间
                //    cellXF.HorizontalAlignment = HorizontalAlignments.Right;//设定文字居中
                //    cell = sheet.Cells.Add(rowCount + _rownumber, 1, "制表日期:" + DateTime.Now.ToString("yyyy年MM月dd日"), cellXF);
                //    meaA = new MergeArea(rowCount + _rownumber, rowCount + _rownumber, 1, _colCount);
                //    sheet.AddMergeArea(meaA);//填加合并单元格
                //}
                foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
                {
                    DataRow _dr = dt.Rows[_xlsModel.RowIndex - 1];
                    int _colindex = 1;
                    int _rowindex = _xlsModel.RowIndex;
                    for (int i = 0; i < _dr.ItemArray.Length; i++)
                    {
                        if (_dr.ItemArray[i].ToString() == "0")
                        {
                            _colindex = i + 1;
                            break;
                        }
                    }

                    cellXF = doc.NewXF();//内容
                    cellXF.TextWrapRight = true; //自动换行
                    cellXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中  
                    cellXF.UseBorder = true;
                    cellXF.TopLineStyle = 1;
                    cellXF.TopLineColor = Colors.Black;
                    cellXF.BottomLineStyle = 1;
                    cellXF.BottomLineColor = Colors.Black;
                    cellXF.LeftLineStyle = 1;
                    cellXF.LeftLineColor = Colors.Black;
                    cellXF.RightLineStyle = 1;
                    cellXF.RightLineColor = Colors.Black;

                    //单元格背景色
                    if (_xlsModel.PColor != Model.XlsModel.PatternColor.no)
                    {
                        switch (_xlsModel.PColor)
                        {
                            case Model.XlsModel.PatternColor.Silver: { cellXF.Pattern = 1; cellXF.PatternColor = Colors.Silver; } break;
                            case Model.XlsModel.PatternColor.Yellow: { cellXF.Pattern = 1; cellXF.PatternColor = Colors.Yellow; } break;
                            case Model.XlsModel.PatternColor.Red: { cellXF.Pattern = 1; cellXF.PatternColor = Colors.Red; } break;
                            case Model.XlsModel.PatternColor.Blue: { cellXF.Pattern = 1; cellXF.PatternColor = Colors.Blue; } break;
                            case Model.XlsModel.PatternColor.Purple: { cellXF.Pattern = 1; cellXF.PatternColor = Colors.Purple; } break;
                        }
                    }


                    switch (_xlsModel.HAlign)
                    {
                        case Model.XlsModel.HorizontalAlign.Left:
                            {
                                cellXF.HorizontalAlignment = HorizontalAlignments.Left;//设定文字居左
                                break;
                            }
                        case Model.XlsModel.HorizontalAlign.Center:
                            {
                                cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中
                                break;
                            }
                        case Model.XlsModel.HorizontalAlign.Right:
                            {
                                cellXF.HorizontalAlignment = HorizontalAlignments.Right;//设定文字居右
                                break;
                            }

                    }

                    switch (_xlsModel.TitleType)
                    {
                        case Model.XlsModel.ContentType.String:
                            {
                                cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, _xlsModel.Title, cellXF);
                                break;
                            }
                        case Model.XlsModel.ContentType.Number:
                            {
                                cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, Convert.ToDouble(_xlsModel.Title), cellXF);
                                break;
                            }
                        default:
                            {
                                cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, _xlsModel.Title, cellXF);
                                break;
                            }
                    }
                    dt.Rows[_rowindex - 1][_colindex - 1] = "1";

                    if (_xlsModel.RowSpan > 1)
                    {
                        MergeRegion(ref sheet, cellXF, _xlsModel.Title, _rowindex + _rownumber, _colindex, _rowindex + _rownumber + _xlsModel.RowSpan - 1, _colindex);
                        //meaA = new MergeArea(_rowindex + _rownumber, _rowindex + _rownumber + _xlsModel.RowSpan - 1, _colindex, _colindex);
                        //sheet.AddMergeArea(meaA);//填加合并单元格
                        for (int i = 1; i < _xlsModel.RowSpan; i++)
                        {
                            dt.Rows[_rowindex - 1 + i][_colindex - 1] = "1";
                        }

                    }
                    if (_xlsModel.ColSpan > 1)
                    {

                        MergeRegion(ref sheet, cellXF, _xlsModel.Title, _rowindex + _rownumber, _colindex, _rowindex + _rownumber, _colindex + _xlsModel.ColSpan - 1);
                        //meaA = new MergeArea(_rowindex + _rownumber, _rowindex + _rownumber, _colindex, _colindex + _xlsModel.ColSpan - 1);
                        //sheet.AddMergeArea(meaA);//填加合并单元格
                        for (int i = 1; i < _xlsModel.ColSpan; i++)
                        {
                            dt.Rows[_rowindex - 1][_colindex - 1 + i] = "1";

                        }


                    }

                }

                //doc.Send(); 
                //addfolder("~/tempfile");

                //string _mappath = HttpContext.Current.Server.MapPath("~/tempfile");
                doc.Save(_mappath, true);
                saveFileName = _mappath + @"\" + doc.FileName;
                //doc.Send();
                //HttpContext.Current.Response.Flush();
                //HttpContext.Current.Response.End();

            }
        }
    }
}
